﻿CREATE PROCEDURE [dbo].[utl_ListStoredProcsUsingTable]
@Table [sysname]=NULL
AS
BEGIN
	DECLARE @True  AS BIT; SET @True  = 1
	DECLARE @False AS BIT; SET @False = 0
	
	DECLARE @SQL NVARCHAR(MAX)
	
	-- must have a table name parameter
	IF @Table IS NULL
	BEGIN
	    SET @SQL = '@Table can not be null.'
	    RAISERROR(@SQL, 16, 1, @Table)
	    RETURN 1
	END
	
	-- make sure table exists    
	IF dbo.fn_TableExists(@Table) = @False
	BEGIN
	    SET @SQL = 'Table ''%s'' does not exist.'
	    RAISERROR(@SQL, 16, 1, @Table)
	    RETURN 1
	END
	
	-- display the list
	SELECT o.[name] AS 'Name',
	       o.type_desc AS 'Type'
	FROM   sys.objects o
	       INNER JOIN sys.sql_modules sm
	            ON  o.[object_id] = sm.[object_id]
	WHERE  o.[type] IN ('FN', 'P', 'V', 'TR', 'TF', 'IF')
	       AND sm.definition LIKE '%' + @Table + '%'  
END

